import json
import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost',
                     user='root',
                     password='root',
                     database='cardesign')

cursor = db.cursor()

with open('../../saleByTopFromSeries.json', 'r', encoding='utf-8') as f:
    listRS = json.load(f)
for index in range(len(listRS)):
    pyearAndMonth = listRS[index]["yearAndMonth"]
    yearAndMonth = pyearAndMonth[:48]
    psalesData = listRS[index]["salesData"]
    salesData = psalesData[:48]
    series = listRS[index]["series"]
    seriesId = listRS[index]["seriesId"]

    # 判断0的个数，多于十二个0的数据不做存贮
    if sum(i == '0' for i in psalesData) > 12:
        continue

    for i in range(0, 48):
        date = yearAndMonth[i].replace("/", "-") + "-1"

        sql = "insert into user_car_sale values('%s','%s','%s',%s)" % (seriesId, series, date, salesData[i])

        print(sql)

        try:
            # 执行sql语句
            cursor.execute(sql)
            # 提交到数据库执行
            db.commit()
        except:
            # 如果发生错误则回滚
            db.rollback()

# 关闭数据库连接
db.close()
